{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "05eb49be-c4d1-45d2-a165-72e44a6cdefd",
   "metadata": {},
   "source": [
    "数据加载代码块\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4f24dd72-35fb-4b3f-b5af-56d9271a9855",
   "metadata": {
    "jupyter": {
     "is_executing": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "成功加载数据文件\n",
      "\n",
      "=== 数据基本信息 ===\n",
      "数据维度: (1020, 13)\n",
      "内存占用: 0.10 MB\n",
      "\n",
      "列名列表:\n",
      "['日期', '姓名', '年龄', '工资', '邮箱', '手机号', '地址', '部门', '产品编码', '订单金额', '客户评分', '物流时效', '评价数量']\n",
      "\n",
      "=== 数据预览（前5行）===\n",
      "           日期  姓名  年龄    工资                   邮箱          手机号  \\\n",
      "0  2023-03-24  赵六  20  9422   user82@example.com  18989136502   \n",
      "1  2024-02-24  吴十  51  5130  user419@example.com  18291673280   \n",
      "2  2023-12-27  赵六  51  7708  user360@example.com  13741980788   \n",
      "3  2024-12-07  张三  29  9569  user706@example.com  19764054579   \n",
      "4  2025-03-18  吴十  41  9473  user807@example.com  19641881979   \n",
      "\n",
      "                 地址   部门     产品编码     订单金额  客户评分  物流时效 评价数量  \n",
      "0  beijing chaoyang  销售部  PRD8380  6201.96  86.8   4.9   93  \n",
      "1      北京市朝阳区建国路29号  市场部  PRD9249  2565.66  82.7  18.1  106  \n",
      "2      北京市朝阳区建国路83号  技术部  PRD8946   5685.1  81.4  38.2  111  \n",
      "3      北京市朝阳区建国路66号  市场部  PRD5204  2607.32  89.3  18.2  111  \n",
      "4      北京市朝阳区建国路38号  人事部  PRD4526  3173.57  92.7  25.7  114  \n",
      "\n",
      "=== 数据类型信息 ===\n",
      "日期      object\n",
      "姓名      object\n",
      "年龄      object\n",
      "工资      object\n",
      "邮箱      object\n",
      "手机号     object\n",
      "地址      object\n",
      "部门      object\n",
      "产品编码    object\n",
      "订单金额    object\n",
      "客户评分    object\n",
      "物流时效    object\n",
      "评价数量    object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# ============== 数据加载代码块 ==============\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')  # 忽略警告信息\n",
    "\n",
    "# 尝试读取数据文件\n",
    "try:\n",
    "    df = pd.read_csv('data/messy_data.csv')\n",
    "    print(\"成功加载数据文件\")\n",
    "except FileNotFoundError:\n",
    "    # 如果文件不存在，则从create_messy_data导入创建函数\n",
    "    from create_messy_data import create_messy_data\n",
    "    df = create_messy_data()\n",
    "    print(\"创建了新的示例数据\")\n",
    "\n",
    "# 显示数据基本信息\n",
    "print(\"\\n=== 数据基本信息 ===\")\n",
    "print(f\"数据维度: {df.shape}\")              # 显示行数和列数\n",
    "print(f\"内存占用: {df.memory_usage().sum() / 1024**2:.2f} MB\")  # 显示内存占用\n",
    "print(\"\\n列名列表:\")\n",
    "print(df.columns.tolist())                  # 显示所有列名\n",
    "\n",
    "# 显示数据预览\n",
    "print(\"\\n=== 数据预览（前5行）===\")\n",
    "print(df.head())\n",
    "\n",
    "# 显示数据类型信息\n",
    "print(\"\\n=== 数据类型信息 ===\")\n",
    "print(df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d72c87e-03eb-4b31-b449-588a6bfef004",
   "metadata": {},
   "source": [
    "缺失值审核"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "4e145272-0b98-43a7-b4bc-372d7555823f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-10T15:43:27.734546Z",
     "start_time": "2024-11-10T15:43:27.716018Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 特殊空值标记检测（转换前） ===\n",
      "日期 列: 特殊空值标记数量 = 1\n",
      "姓名 列: 特殊空值标记数量 = 0\n",
      "年龄 列: 特殊空值标记数量 = 3\n",
      "工资 列: 特殊空值标记数量 = 4\n",
      "邮箱 列: 特殊空值标记数量 = 0\n",
      "手机号 列: 特殊空值标记数量 = 2\n",
      "地址 列: 特殊空值标记数量 = 3\n",
      "部门 列: 特殊空值标记数量 = 0\n",
      "产品编码 列: 特殊空值标记数量 = 3\n",
      "订单金额 列: 特殊空值标记数量 = 4\n",
      "客户评分 列: 特殊空值标记数量 = 3\n",
      "物流时效 列: 特殊空值标记数量 = 2\n",
      "评价数量 列: 特殊空值标记数量 = 4\n",
      "\n",
      "=== 缺失值统计（转换后） ===\n",
      "      缺失值数量  缺失值比例    数据类型\n",
      "日期        1   0.10  object\n",
      "姓名        0   0.00  object\n",
      "年龄        3   0.29  object\n",
      "工资        4   0.39  object\n",
      "邮箱        0   0.00  object\n",
      "手机号       2   0.20  object\n",
      "地址        3   0.29  object\n",
      "部门        0   0.00  object\n",
      "产品编码      3   0.29  object\n",
      "订单金额      4   0.39  object\n",
      "客户评分      3   0.29  object\n",
      "物流时效      2   0.20  object\n",
      "评价数量      4   0.39  object\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 改进的缺失值检测函数\n",
    "def is_special_null(value):\n",
    "    \"\"\"识别特殊的空值标记\"\"\"\n",
    "    special_nulls = [\n",
    "        'N/A', 'NA', 'null', 'NULL', 'none', 'NONE', '', 'invalid_date',\n",
    "        'invalid_age', 'invalid_salary', 'pending', '待审核', '未评分',\n",
    "        '待确认', 'error', 'hidden', '未知'\n",
    "    ]\n",
    "    # 检测是否为 NaN 或是否在特殊标记列表中\n",
    "    return pd.isnull(value) or str(value).strip() in special_nulls\n",
    "\n",
    "# 1. 在转换之前，检测每列中有多少特殊空值标记\n",
    "print(\"=== 特殊空值标记检测（转换前） ===\")\n",
    "for column in df.columns:\n",
    "    special_null_count = df[column].apply(lambda x: is_special_null(x)).sum()\n",
    "    print(f\"{column} 列: 特殊空值标记数量 = {special_null_count}\")\n",
    "\n",
    "# 2. 将自定义空值标记转换为 NaN\n",
    "df = df.applymap(lambda x: np.nan if is_special_null(x) else x)\n",
    "\n",
    "# 3. 重新计算缺失值统计\n",
    "missing_stats = pd.DataFrame({\n",
    "    '缺失值数量': df.isnull().sum(),\n",
    "    '缺失值比例': (df.isnull().sum() / len(df) * 100).round(2),\n",
    "    '数据类型': df.dtypes\n",
    "})\n",
    "\n",
    "print(\"\\n=== 缺失值统计（转换后） ===\")\n",
    "print(missing_stats)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df909040-76d1-4751-ab96-6abbd1bb2d03",
   "metadata": {},
   "source": [
    "缺失值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "200e1402-d048-4a0d-b04e-11ae0f503507",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-10T15:43:27.824154Z",
     "start_time": "2024-11-10T15:43:27.793043Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "处理 年龄 列:\n",
      "  - 已将非数值数据转换为NaN并用中位数 41.00 填充\n",
      "  - 列的唯一值: [20. 51. 29. 41. 31.]...\n",
      "  - 剩余缺失值数量: 0\n",
      "\n",
      "处理 工资 列:\n",
      "  - 已将非数值数据转换为NaN并用中位数 12564.00 填充\n",
      "  - 列的唯一值: [9422. 5130. 7708. 9569. 9473.]...\n",
      "  - 剩余缺失值数量: 0\n",
      "\n",
      "处理 订单金额 列:\n",
      "  - 已将非数值数据转换为NaN并用中位数 3011.11 填充\n",
      "  - 列的唯一值: [6201.96 2565.66 5685.1  2607.32 3173.57]...\n",
      "  - 剩余缺失值数量: 0\n",
      "\n",
      "处理 客户评分 列:\n",
      "  - 已将非数值数据转换为NaN并用中位数 85.40 填充\n",
      "  - 列的唯一值: [86.8 82.7 81.4 89.3 92.7]...\n",
      "  - 剩余缺失值数量: 0\n",
      "\n",
      "处理 物流时效 列:\n",
      "  - 已将非数值数据转换为NaN并用中位数 16.80 填充\n",
      "  - 列的唯一值: [ 4.9 18.1 38.2 18.2 25.7]...\n",
      "  - 剩余缺失值数量: 0\n",
      "\n",
      "处理 评价数量 列:\n",
      "  - 已将非数值数据转换为NaN并用中位数 99.00 填充\n",
      "  - 列的唯一值: [ 93. 106. 111. 114.  91.]...\n",
      "  - 剩余缺失值数量: 0\n",
      "\n",
      "列 '部门' 的缺失值已用众数 '技术部' 填充\n",
      "\n",
      "列 '产品编码' 的缺失值已用众数 'PRD6136' 填充\n",
      "已删除 '姓名' 列中包含缺失值的行\n",
      "已删除 '手机号' 列中包含缺失值的行\n",
      "\n",
      "=== 缺失值处理后的统计 ===\n",
      "      处理后缺失值数量  处理后缺失值比例\n",
      "日期           0      0.00\n",
      "姓名           0      0.00\n",
      "年龄           0      0.00\n",
      "工资           0      0.00\n",
      "邮箱           0      0.00\n",
      "手机号          0      0.00\n",
      "地址           3      0.29\n",
      "部门           0      0.00\n",
      "产品编码         0      0.00\n",
      "订单金额         0      0.00\n",
      "客户评分         0      0.00\n",
      "物流时效         0      0.00\n",
      "评价数量         0      0.00\n",
      "\n",
      "处理前数据行数: 1020\n",
      "处理后数据行数: 1018\n",
      "\n",
      "处理后的数据已保存至: data/processed/data_after_missing_handled.csv\n",
      "\n",
      "处理报告已保存至: data/processed/missing_values_handling_report.txt\n",
      "\n",
      "=== 处理后的数据样本（前5行）===\n",
      "           日期  姓名  年龄      工资                   邮箱          手机号  \\\n",
      "0  2023-03-24  赵六  20  9422.0   user82@example.com  18989136502   \n",
      "1  2024-02-24  吴十  51  5130.0  user419@example.com  18291673280   \n",
      "2  2023-12-27  赵六  51  7708.0  user360@example.com  13741980788   \n",
      "3  2024-12-07  张三  29  9569.0  user706@example.com  19764054579   \n",
      "4  2025-03-18  吴十  41  9473.0  user807@example.com  19641881979   \n",
      "\n",
      "                 地址   部门     产品编码     订单金额  客户评分  物流时效  评价数量  \n",
      "0  beijing chaoyang  销售部  PRD8380  6201.96  86.8   4.9    93  \n",
      "1      北京市朝阳区建国路29号  市场部  PRD9249  2565.66  82.7  18.1   106  \n",
      "2      北京市朝阳区建国路83号  技术部  PRD8946  5685.10  81.4  38.2   111  \n",
      "3      北京市朝阳区建国路66号  市场部  PRD5204  2607.32  89.3  18.2   111  \n",
      "4      北京市朝阳区建国路38号  人事部  PRD4526  3173.57  92.7  25.7   114  \n"
     ]
    }
   ],
   "source": [
    "# ============== 缺失值处理代码块 ==============\n",
    "# 创建数据副本进行处理\n",
    "df_cleaned = df.copy()\n",
    "\n",
    "# 1. 处理数值列中的异常数据和缺失值\n",
    "numeric_columns = ['年龄', '工资', '订单金额', '客户评分', '物流时效', '评价数量']  # 明确指定数值列\n",
    "for col in numeric_columns:\n",
    "    print(f\"\\n处理 {col} 列:\")\n",
    "    # 1.1 将非数值数据转换为NaN\n",
    "    # 先将列转换为字符串\n",
    "    df_cleaned[col] = df_cleaned[col].astype(str)\n",
    "    # 使用数值模式匹配，保留合法的数值（包括负数和小数）\n",
    "    mask = df_cleaned[col].str.match(r'^-?\\d*\\.?\\d+$')\n",
    "    df_cleaned.loc[~mask, col] = np.nan\n",
    "    # 将列转换为float类型\n",
    "    df_cleaned[col] = df_cleaned[col].astype(float)\n",
    "    \n",
    "    # 1.2 使用中位数填充缺失值\n",
    "    median_value = df_cleaned[col].median()\n",
    "    df_cleaned[col] = df_cleaned[col].fillna(median_value)\n",
    "    print(f\"  - 已将非数值数据转换为NaN并用中位数 {median_value:.2f} 填充\")\n",
    "    \n",
    "    # 1.3 显示处理结果\n",
    "    print(f\"  - 列的唯一值: {df_cleaned[col].unique()[:5]}...\")  # 只显示前5个唯一值\n",
    "    print(f\"  - 剩余缺失值数量: {df_cleaned[col].isnull().sum()}\")\n",
    "\n",
    "# 2. 处理分类列的缺失值\n",
    "categorical_columns = ['部门', '产品编码']\n",
    "for col in categorical_columns:\n",
    "    # 使用众数填充分类列的缺失值\n",
    "    mode_value = df_cleaned[col].mode()[0]\n",
    "    df_cleaned[col] = df_cleaned[col].fillna(mode_value)\n",
    "    print(f\"\\n列 '{col}' 的缺失值已用众数 '{mode_value}' 填充\")\n",
    "\n",
    "# 3. 处理文本列的特殊空值\n",
    "text_columns = ['姓名', '地址', '邮箱', '手机号']\n",
    "for col in text_columns:\n",
    "    # 将特殊空值标记转换为NaN\n",
    "    df_cleaned[col] = df_cleaned[col].replace(['N/A', 'NA', 'null', 'NULL', 'none', 'NONE', ''], np.nan)\n",
    "    # 对于关键信息，可以选择删除包含缺失值的行\n",
    "    if col in ['姓名', '手机号']:  # 假设这些是必需的字段\n",
    "        df_cleaned = df_cleaned.dropna(subset=[col])\n",
    "        print(f\"已删除 '{col}' 列中包含缺失值的行\")\n",
    "\n",
    "# 4. 验证处理结果\n",
    "print(\"\\n=== 缺失值处理后的统计 ===\")\n",
    "missing_after = pd.DataFrame({\n",
    "    '处理后缺失值数量': df_cleaned.isnull().sum(),\n",
    "    '处理后缺失值比例': (df_cleaned.isnull().sum() / len(df_cleaned) * 100).round(2)\n",
    "})\n",
    "print(missing_after)\n",
    "\n",
    "# 5. 数据类型转换\n",
    "# 将数值列转换为适当的类型\n",
    "for col in numeric_columns:\n",
    "    if col in ['年龄', '评价数量']:  # 应该是整数的列\n",
    "        df_cleaned[col] = df_cleaned[col].round().astype('Int64')\n",
    "    else:  # 可以是小数的列\n",
    "        df_cleaned[col] = df_cleaned[col].astype('float64')\n",
    "\n",
    "# 显示处理前后的数据量对比\n",
    "print(f\"\\n处理前数据行数: {len(df)}\")\n",
    "print(f\"处理后数据行数: {len(df_cleaned)}\")\n",
    "\n",
    "# 6. 保存处理后的数据\n",
    "# 确保输出目录存在\n",
    "import os\n",
    "if not os.path.exists('data/processed'):\n",
    "    os.makedirs('data/processed')\n",
    "\n",
    "# 保存处理后的数据到新文件\n",
    "output_file = 'data/processed/data_after_missing_handled.csv'\n",
    "df_cleaned.to_csv(output_file, index=False, encoding='utf-8')\n",
    "print(f\"\\n处理后的数据已保存至: {output_file}\")\n",
    "\n",
    "# 7. 生成处理报告\n",
    "report = f\"\"\"\n",
    "========== 缺失值处理报告 ==========\n",
    "处理时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n",
    "\n",
    "1. 数据规模变化:\n",
    "   - 原始数据行数: {len(df)}\n",
    "   - 处理后数据行数: {len(df_cleaned)}\n",
    "   - 减少行数: {len(df) - len(df_cleaned)}\n",
    "\n",
    "2. 处理方法说明:\n",
    "   - 数值列: 先转换非数值为NaN，然后用中位数填充\n",
    "   - 分类列: 使用众数填充\n",
    "   - 文本列: 特殊空值转换为NaN，关键字段行删除\n",
    "\n",
    "3. 数据类型转换:\n",
    "   - 年龄和评价数量: 转换为整数\n",
    "   - 其他数值列: 保留为浮点数\n",
    "\n",
    "4. 处理结果文件位置:\n",
    "   {output_file}\n",
    "\n",
    "5. 各列缺失值处理结果:\n",
    "{missing_after.to_string()}\n",
    "\"\"\"\n",
    "\n",
    "# 保存处理报告\n",
    "report_file = 'data/processed/missing_values_handling_report.txt'\n",
    "with open(report_file, 'w', encoding='utf-8') as f:\n",
    "    f.write(report)\n",
    "print(f\"\\n处理报告已保存至: {report_file}\")\n",
    "\n",
    "# 8. 显示处理后的数据样本\n",
    "print(\"\\n=== 处理后的数据样本（前5行）===\")\n",
    "print(df_cleaned.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "647ecfa3-7397-45be-8efe-136c05f130ac",
   "metadata": {},
   "source": [
    "一致性审核"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "84111f89-64a5-4f7e-9b90-ff1ab84f4002",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-10T15:43:27.861965Z",
     "start_time": "2024-11-10T15:43:27.848467Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 开始数据一致性审核 ===\n",
      "\n",
      "1. 日期格式审核:\n",
      "发现 6 个不规范的日期格式:\n",
      "137      01/19/2023\n",
      "181         2023-01\n",
      "254      01-15-2023\n",
      "314        20230117\n",
      "587    invalid_date\n",
      "Name: 日期, dtype: object\n",
      "\n",
      "2. 姓名格式审核:\n",
      "发现 10 个不规范的姓名格式:\n",
      "41     ZHANG SAN\n",
      "199          王五 \n",
      "250        李   四\n",
      "301           张三\n",
      "345          李四 \n",
      "Name: 姓名, dtype: object\n",
      "\n",
      "3. 手机号格式审核:\n",
      "发现 8 个不规范的手机号格式:\n",
      "72     086-13888888888\n",
      "128        abc12345678\n",
      "164    +86-13888888888\n",
      "183      138 8888 8888\n",
      "196         2345678901\n",
      "Name: 手机号, dtype: object\n",
      "\n",
      "4. 邮箱格式审核:\n",
      "发现 8 个不规范的邮箱格式:\n",
      "18           @example.com\n",
      "68                  user@\n",
      "430              user@com\n",
      "539     user@example.com \n",
      "938             user@.com\n",
      "Name: 邮箱, dtype: object\n",
      "\n",
      "5. 部门名称审核:\n",
      "当前部门名称变体:\n",
      "部门\n",
      "技术部          266\n",
      "销售部          261\n",
      "人事部          251\n",
      "市场部          230\n",
      "人事部门           1\n",
      "销售部门           1\n",
      "Tech           1\n",
      "HR             1\n",
      "SALES          1\n",
      "Sales          1\n",
      "技术部门           1\n",
      "市场部门           1\n",
      "Marketing      1\n",
      "sales          1\n",
      "Name: count, dtype: int64\n",
      "\n",
      "6. 产品编码审核:\n",
      "发现 7 个不规范的产品编码:\n",
      "219    PRD_1234\n",
      "319     Prd1234\n",
      "458    prd_1234\n",
      "531     prd1234\n",
      "709     PRD1234\n",
      "Name: 产品编码, dtype: object\n",
      "\n",
      "=== 一致性审核总结 ===\n",
      "日期格式问题: 6\n",
      "姓名格式问题: 10\n",
      "手机号格式问题: 8\n",
      "邮箱格式问题: 8\n",
      "产品编码问题: 7\n",
      "部门名称变体数: 14\n"
     ]
    }
   ],
   "source": [
    "# ============== 一致性审核代码块 ==============\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "from datetime import datetime\n",
    "\n",
    "print(\"=== 开始数据一致性审核 ===\")\n",
    "\n",
    "# 1. 日期格式一致性审核\n",
    "print(\"\\n1. 日期格式审核:\")\n",
    "date_patterns = df_cleaned['日期'].str.extract(r'(\\d{4})[-/.](\\d{1,2})[-/.](\\d{1,2})')\n",
    "invalid_dates = df_cleaned[date_patterns.isnull().any(axis=1)]['日期']\n",
    "print(f\"发现 {len(invalid_dates)} 个不规范的日期格式:\")\n",
    "print(invalid_dates.head())\n",
    "\n",
    "# 2. 名称格式一致性审核\n",
    "print(\"\\n2. 姓名格式审核:\")\n",
    "# 检查中文姓名格式\n",
    "chinese_name_pattern = re.compile(r'^[\\u4e00-\\u9fa5]{2,4}$')\n",
    "invalid_names = df_cleaned[~df_cleaned['姓名'].str.match(chinese_name_pattern, na=False)]\n",
    "print(f\"发现 {len(invalid_names)} 个不规范的姓名格式:\")\n",
    "print(invalid_names['姓名'].head())\n",
    "\n",
    "# 3. 手机号格式一致性审核\n",
    "print(\"\\n3. 手机号格式审核:\")\n",
    "phone_pattern = re.compile(r'^1[3-9]\\d{9}$')\n",
    "invalid_phones = df_cleaned[~df_cleaned['手机号'].str.match(phone_pattern, na=False)]\n",
    "print(f\"发现 {len(invalid_phones)} 个不规范的手机号格式:\")\n",
    "print(invalid_phones['手机号'].head())\n",
    "\n",
    "# 4. 邮箱格式一致性审核\n",
    "print(\"\\n4. 邮箱格式审核:\")\n",
    "email_pattern = re.compile(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$')\n",
    "invalid_emails = df_cleaned[~df_cleaned['邮箱'].str.match(email_pattern, na=False)]\n",
    "print(f\"发现 {len(invalid_emails)} 个不规范的邮箱格式:\")\n",
    "print(invalid_emails['邮箱'].head())\n",
    "\n",
    "# 5. 部门名称一致性审核\n",
    "print(\"\\n5. 部门名称审核:\")\n",
    "dept_variants = df_cleaned['部门'].value_counts()\n",
    "print(\"当前部门名称变体:\")\n",
    "print(dept_variants)\n",
    "\n",
    "# 6. 产品编码一致性审核\n",
    "print(\"\\n6. 产品编码审核:\")\n",
    "code_pattern = re.compile(r'^PRD\\d{4}$')\n",
    "invalid_codes = df_cleaned[~df_cleaned['产品编码'].str.match(code_pattern, na=False)]\n",
    "print(f\"发现 {len(invalid_codes)} 个不规范的产品编码:\")\n",
    "print(invalid_codes['产品编码'].head())\n",
    "\n",
    "# 7. 生成一致性审核报告\n",
    "consistency_report = {\n",
    "    '日期格式问题': len(invalid_dates),\n",
    "    '姓名格式问题': len(invalid_names),\n",
    "    '手机号格式问题': len(invalid_phones),\n",
    "    '邮箱格式问题': len(invalid_emails),\n",
    "    '产品编码问题': len(invalid_codes),\n",
    "    '部门名称变体数': len(dept_variants)\n",
    "}\n",
    "\n",
    "print(\"\\n=== 一致性审核总结 ===\")\n",
    "for item, count in consistency_report.items():\n",
    "    print(f\"{item}: {count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d49ae404-9306-4ee3-9915-98b7a9ecf9d1",
   "metadata": {},
   "source": [
    "一致性处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b036c851-4177-4d2c-ae9d-d076f95e4c32",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-10T15:43:28.074944Z",
     "start_time": "2024-11-10T15:43:27.866833Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 开始数据一致性处理 ===\n",
      "\n",
      "1. 处理日期格式:\n",
      "日期格式已标准化为 YYYY-MM-DD\n",
      "\n",
      "2. 处理姓名格式:\n",
      "姓名格式已标准化\n",
      "\n",
      "3. 处理手机号格式:\n",
      "手机号格式已标准化\n",
      "\n",
      "4. 处理邮箱格式:\n",
      "邮箱格式已标准化\n",
      "\n",
      "5. 处理部门名称:\n",
      "部门名称已标准化\n",
      "\n",
      "6. 处理产品编码:\n",
      "产品编码已标准化\n",
      "\n",
      "=== 验证处理结果 ===\n",
      "\n",
      "日期的唯一值:\n",
      "日期\n",
      "2023-01-01    4\n",
      "2023-03-24    2\n",
      "2023-09-19    2\n",
      "2023-01-15    2\n",
      "2024-02-24    2\n",
      "Name: count, dtype: int64\n",
      "\n",
      "姓名的唯一值:\n",
      "姓名\n",
      "周九    147\n",
      "钱七    138\n",
      "李四    132\n",
      "吴十    125\n",
      "赵六    124\n",
      "Name: count, dtype: int64\n",
      "\n",
      "手机号的唯一值:\n",
      "手机号\n",
      "18989136502    2\n",
      "15122955324    2\n",
      "18291673280    2\n",
      "15689220202    2\n",
      "15887822880    2\n",
      "Name: count, dtype: int64\n",
      "\n",
      "邮箱的唯一值:\n",
      "邮箱\n",
      "user82@example.com     2\n",
      "user294@example.com    2\n",
      "user419@example.com    2\n",
      "user944@example.com    2\n",
      "user772@example.com    2\n",
      "Name: count, dtype: int64\n",
      "\n",
      "部门的唯一值:\n",
      "部门\n",
      "技术部    268\n",
      "销售部    265\n",
      "人事部    253\n",
      "市场部    232\n",
      "Name: count, dtype: int64\n",
      "\n",
      "产品编码的唯一值:\n",
      "产品编码\n",
      "PRD1234    7\n",
      "PRD6136    6\n",
      "PRD8380    3\n",
      "PRD4085    2\n",
      "PRD2077    2\n",
      "Name: count, dtype: int64\n",
      "\n",
      "标准化后的数据已保存至: data/processed/data_after_standardization.csv\n",
      "\n",
      "标准化处理报告已保存至: data/processed/standardization_report.txt\n"
     ]
    }
   ],
   "source": [
    "# ============== 一致性处理代码块 ==============\n",
    "# 创建数据副本\n",
    "df_standardized = df_cleaned.copy()\n",
    "\n",
    "print(\"=== 开始数据一致性处理 ===\")\n",
    "\n",
    "# 1. 标准化日期格式\n",
    "def standardize_date(date_str):\n",
    "    if pd.isna(date_str):\n",
    "        return None\n",
    "    # 尝试各种日期格式\n",
    "    try:\n",
    "        # 先尝试直接解析\n",
    "        return pd.to_datetime(date_str).strftime('%Y-%m-%d')\n",
    "    except:\n",
    "        # 如果解析失败，返回None\n",
    "        return None\n",
    "\n",
    "print(\"\\n1. 处理日期格式:\")\n",
    "df_standardized['日期'] = df_standardized['日期'].apply(standardize_date)\n",
    "print(\"日期格式已标准化为 YYYY-MM-DD\")\n",
    "\n",
    "# 2. 标准化姓名格式\n",
    "def standardize_name(name):\n",
    "    if pd.isna(name):\n",
    "        return None\n",
    "    # 去除空白字符\n",
    "    name = re.sub(r'\\s+', '', str(name))\n",
    "    # 转换为中文名称格式（如果是拼音）\n",
    "    name_mapping = {\n",
    "        'zhang san': '张三', 'li si': '李四', 'wang wu': '王五',\n",
    "        'zhao liu': '赵六', 'qian qi': '钱七'\n",
    "    }\n",
    "    return name_mapping.get(name.lower(), name)\n",
    "\n",
    "print(\"\\n2. 处理姓名格式:\")\n",
    "df_standardized['姓名'] = df_standardized['姓名'].apply(standardize_name)\n",
    "print(\"姓名格式已标准化\")\n",
    "\n",
    "# 3. 标准化手机号格式\n",
    "def standardize_phone(phone):\n",
    "    if pd.isna(phone):\n",
    "        return None\n",
    "    # 提取数字\n",
    "    digits = re.sub(r'\\D', '', str(phone))\n",
    "    # 如果是11位手机号，且以1开头\n",
    "    if len(digits) == 11 and digits.startswith('1'):\n",
    "        return digits\n",
    "    return None\n",
    "\n",
    "print(\"\\n3. 处理手机号格式:\")\n",
    "df_standardized['手机号'] = df_standardized['手机号'].apply(standardize_phone)\n",
    "print(\"手机号格式已标准化\")\n",
    "\n",
    "# 4. 标准化邮箱格式\n",
    "def standardize_email(email):\n",
    "    if pd.isna(email):\n",
    "        return None\n",
    "    # 去除首尾空白\n",
    "    email = str(email).strip().lower()\n",
    "    # 检查基本邮箱格式\n",
    "    if re.match(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$', email):\n",
    "        return email\n",
    "    return None\n",
    "\n",
    "print(\"\\n4. 处理邮箱格式:\")\n",
    "df_standardized['邮箱'] = df_standardized['邮箱'].apply(standardize_email)\n",
    "print(\"邮箱格式已标准化\")\n",
    "\n",
    "# 5. 标准化部门名称\n",
    "department_mapping = {\n",
    "    'Sales': '销售部', 'SALES': '销售部', 'sales': '销售部', '销售部门': '销售部',\n",
    "    'Tech': '技术部', 'TECH': '技术部', '技术部门': '技术部',\n",
    "    'Marketing': '市场部', 'MARKETING': '市场部', '市场部门': '市场部',\n",
    "    'HR': '人事部', 'Human Resources': '人事部', '人事部门': '人事部'\n",
    "}\n",
    "\n",
    "print(\"\\n5. 处理部门名称:\")\n",
    "df_standardized['部门'] = df_standardized['部门'].replace(department_mapping)\n",
    "print(\"部门名称已标准化\")\n",
    "\n",
    "# 6. 标准化产品编码\n",
    "def standardize_product_code(code):\n",
    "    if pd.isna(code):\n",
    "        return None\n",
    "    # 提取数字部分\n",
    "    digits = re.search(r'\\d{4}', str(code))\n",
    "    if digits:\n",
    "        return f\"PRD{digits.group()}\"\n",
    "    return None\n",
    "\n",
    "print(\"\\n6. 处理产品编码:\")\n",
    "df_standardized['产品编码'] = df_standardized['产品编码'].apply(standardize_product_code)\n",
    "print(\"产品编码已标准化\")\n",
    "\n",
    "# 7. 验证处理结果\n",
    "print(\"\\n=== 验证处理结果 ===\")\n",
    "for column in ['日期', '姓名', '手机号', '邮箱', '部门', '产品编码']:\n",
    "    print(f\"\\n{column}的唯一值:\")\n",
    "    print(df_standardized[column].value_counts().head())\n",
    "\n",
    "# 8. 保存标准化后的数据\n",
    "output_file = 'data/processed/data_after_standardization.csv'\n",
    "df_standardized.to_csv(output_file, index=False, encoding='utf-8')\n",
    "print(f\"\\n标准化后的数据已保存至: {output_file}\")\n",
    "\n",
    "# 9. 生成处理报告\n",
    "standardization_report = f\"\"\"\n",
    "========== 数据标准化处理报告 ==========\n",
    "处理时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n",
    "\n",
    "1. 数据标准化范围:\n",
    "   - 日期格式: 统一为 YYYY-MM-DD\n",
    "   - 姓名格式: 统一为中文姓名\n",
    "   - 手机号格式: 统一为11位数字\n",
    "   - 邮箱格式: 统一为小写，去除空格\n",
    "   - 部门名称: 统一为中文部门名\n",
    "   - 产品编码: 统一为PRD+4位数字\n",
    "\n",
    "2. 处理结果统计:\n",
    "   日期格式标准化: {df_standardized['日期'].notna().sum()} 条有效记录\n",
    "   姓名格式标准化: {df_standardized['姓名'].notna().sum()} 条有效记录\n",
    "   手机号格式标准化: {df_standardized['手机号'].notna().sum()} 条有效记录\n",
    "   邮箱格式标准化: {df_standardized['邮箱'].notna().sum()} 条有效记录\n",
    "   部门名称标准化: {df_standardized['部门'].notna().sum()} 条有效记录\n",
    "   产品编码标准化: {df_standardized['产品编码'].notna().sum()} 条有效记录\n",
    "\n",
    "3. 数据规模:\n",
    "   处理前记录数: {len(df_cleaned)}\n",
    "   处理后记录数: {len(df_standardized)}\n",
    "\n",
    "4. 处理结果文件位置:\n",
    "   {output_file}\n",
    "\"\"\"\n",
    "\n",
    "# 保存处理报告\n",
    "report_file = 'data/processed/standardization_report.txt'\n",
    "with open(report_file, 'w', encoding='utf-8') as f:\n",
    "    f.write(standardization_report)\n",
    "print(f\"\\n标准化处理报告已保存至: {report_file}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58a57322-8ef5-47a9-a23e-e4bcf1d20d1a",
   "metadata": {},
   "source": [
    "异常值审核"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "58c55679-ff56-4563-a38e-6a88d6b172a5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-10T15:43:28.454912Z",
     "start_time": "2024-11-10T15:43:28.099296Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 开始异常值审核 ===\n",
      "\n",
      "检查 年龄 的异常值:\n",
      "基本统计量:\n",
      "  平均值: 41.14\n",
      "  中位数: 41.00\n",
      "  标准差: 32.88\n",
      "  最小值: -1.00\n",
      "  最大值: 999.00\n",
      "\n",
      "异常值边界:\n",
      "  下界: -1.50\n",
      "  上界: 82.50\n",
      "\n",
      "发现 3 条异常记录 (0.29%)\n",
      "\n",
      "检查 工资 的异常值:\n",
      "基本统计量:\n",
      "  平均值: 12424.79\n",
      "  中位数: 12564.00\n",
      "  标准差: 4305.54\n",
      "  最小值: -5000.00\n",
      "  最大值: 19925.00\n",
      "\n",
      "异常值边界:\n",
      "  下界: -2870.62\n",
      "  上界: 27820.38\n",
      "\n",
      "发现 1 条异常记录 (0.10%)\n",
      "\n",
      "检查 订单金额 的异常值:\n",
      "基本统计量:\n",
      "  平均值: 4557.58\n",
      "  中位数: 3011.11\n",
      "  标准差: 31313.63\n",
      "  最小值: -50.00\n",
      "  最大值: 1000000.00\n",
      "\n",
      "异常值边界:\n",
      "  下界: -1575.25\n",
      "  上界: 8113.30\n",
      "\n",
      "发现 45 条异常记录 (4.42%)\n",
      "\n",
      "检查 客户评分 的异常值:\n",
      "基本统计量:\n",
      "  平均值: 84.83\n",
      "  中位数: 85.40\n",
      "  标准差: 10.13\n",
      "  最小值: -10.00\n",
      "  最大值: 150.00\n",
      "\n",
      "异常值边界:\n",
      "  下界: 58.05\n",
      "  上界: 112.05\n",
      "\n",
      "发现 6 条异常记录 (0.59%)\n",
      "\n",
      "检查 物流时效 的异常值:\n",
      "基本统计量:\n",
      "  平均值: 20.07\n",
      "  中位数: 16.80\n",
      "  标准差: 15.55\n",
      "  最小值: -2.00\n",
      "  最大值: 240.00\n",
      "\n",
      "异常值边界:\n",
      "  下界: -14.90\n",
      "  上界: 51.50\n",
      "\n",
      "发现 33 条异常记录 (3.24%)\n",
      "\n",
      "检查 评价数量 的异常值:\n",
      "基本统计量:\n",
      "  平均值: 1081.80\n",
      "  中位数: 99.00\n",
      "  标准差: 31338.81\n",
      "  最小值: -1.00\n",
      "  最大值: 999999.00\n",
      "\n",
      "异常值边界:\n",
      "  下界: 73.50\n",
      "  上界: 125.50\n",
      "\n",
      "发现 7 条异常记录 (0.69%)\n",
      "\n",
      "=== 业务规则异常检测 ===\n",
      "\n",
      "年龄异常（<18 或 >70）: 5 条\n",
      "工资异常（<3000 或 >50000）: 1 条\n",
      "评分异常（<0 或 >100）: 2 条\n",
      "物流时效异常（<0 或 >72小时）: 8 条\n",
      "\n",
      "=== 生成异常值审核报告 ===\n",
      "\n",
      "异常值审核报告已保存至: data/processed/outlier_audit_report.txt\n"
     ]
    }
   ],
   "source": [
    "# ============== 异常值审核代码块 ==============\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from scipy import stats\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "print(\"=== 开始异常值审核 ===\")\n",
    "\n",
    "# 1. 数值列异常值检测\n",
    "# 需要检查异常值的数值列\n",
    "numeric_columns = ['年龄', '工资', '订单金额', '客户评分', '物流时效', '评价数量']\n",
    "\n",
    "# 创建异常值报告字典\n",
    "outlier_report = {}\n",
    "\n",
    "for column in numeric_columns:\n",
    "    print(f\"\\n检查 {column} 的异常值:\")\n",
    "    \n",
    "    # 计算基本统计量\n",
    "    stats_data = {\n",
    "        '平均值': df_standardized[column].mean(),\n",
    "        '中位数': df_standardized[column].median(),\n",
    "        '标准差': df_standardized[column].std(),\n",
    "        '最小值': df_standardized[column].min(),\n",
    "        '最大值': df_standardized[column].max()\n",
    "    }\n",
    "    \n",
    "    # 计算四分位数\n",
    "    Q1 = df_standardized[column].quantile(0.25)\n",
    "    Q3 = df_standardized[column].quantile(0.75)\n",
    "    IQR = Q3 - Q1\n",
    "    \n",
    "    # 定义异常值边界\n",
    "    lower_bound = Q1 - 1.5 * IQR\n",
    "    upper_bound = Q3 + 1.5 * IQR\n",
    "    \n",
    "    # 找出异常值\n",
    "    outliers = df_standardized[\n",
    "        (df_standardized[column] < lower_bound) | \n",
    "        (df_standardized[column] > upper_bound)\n",
    "    ]\n",
    "    \n",
    "    # 收集异常值信息\n",
    "    outlier_report[column] = {\n",
    "        '统计量': stats_data,\n",
    "        '四分位数': {'Q1': Q1, 'Q3': Q3, 'IQR': IQR},\n",
    "        '异常值边界': {'下界': lower_bound, '上界': upper_bound},\n",
    "        '异常值数量': len(outliers),\n",
    "        '异常值比例': (len(outliers) / len(df_standardized) * 100),\n",
    "        '异常值示例': outliers[column].head().tolist()\n",
    "    }\n",
    "    \n",
    "    # 打印异常值检测结果\n",
    "    print(f\"基本统计量:\")\n",
    "    for k, v in stats_data.items():\n",
    "        print(f\"  {k}: {v:.2f}\")\n",
    "    print(f\"\\n异常值边界:\")\n",
    "    print(f\"  下界: {lower_bound:.2f}\")\n",
    "    print(f\"  上界: {upper_bound:.2f}\")\n",
    "    print(f\"\\n发现 {len(outliers)} 条异常记录 ({(len(outliers)/len(df_standardized)*100):.2f}%)\")\n",
    "    \n",
    "    # 绘制箱线图\n",
    "    plt.figure(figsize=(10, 4))\n",
    "    sns.boxplot(x=df_standardized[column])\n",
    "    plt.title(f\"{column} 的箱线图\")\n",
    "    plt.savefig(f'data/processed/boxplot_{column}.png')\n",
    "    plt.close()\n",
    "\n",
    "# 2. 业务规则异常检测\n",
    "print(\"\\n=== 业务规则异常检测 ===\")\n",
    "\n",
    "# 2.1 检查年龄范围\n",
    "age_violations = df_standardized[\n",
    "    (df_standardized['年龄'] < 18) | \n",
    "    (df_standardized['年龄'] > 70)\n",
    "]\n",
    "print(f\"\\n年龄异常（<18 或 >70）: {len(age_violations)} 条\")\n",
    "\n",
    "# 2.2 检查工资范围\n",
    "salary_violations = df_standardized[\n",
    "    (df_standardized['工资'] < 3000) | \n",
    "    (df_standardized['工资'] > 50000)\n",
    "]\n",
    "print(f\"工资异常（<3000 或 >50000）: {len(salary_violations)} 条\")\n",
    "\n",
    "# 2.3 检查评分范围\n",
    "score_violations = df_standardized[\n",
    "    (df_standardized['客户评分'] < 0) | \n",
    "    (df_standardized['客户评分'] > 100)\n",
    "]\n",
    "print(f\"评分异常（<0 或 >100）: {len(score_violations)} 条\")\n",
    "\n",
    "# 2.4 检查物流时效异常\n",
    "delivery_violations = df_standardized[\n",
    "    (df_standardized['物流时效'] < 0) | \n",
    "    (df_standardized['物流时效'] > 72)  # 假设正常配送不超过72小时\n",
    "]\n",
    "print(f\"物流时效异常（<0 或 >72小时）: {len(delivery_violations)} 条\")\n",
    "\n",
    "# 3. 生成异常值审核报告\n",
    "print(\"\\n=== 生成异常值审核报告 ===\")\n",
    "\n",
    "audit_report = f\"\"\"\n",
    "========== 异常值审核报告 ==========\n",
    "生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n",
    "\n",
    "1. 统计异常检测结果：\n",
    "\"\"\"\n",
    "\n",
    "for column, report in outlier_report.items():\n",
    "    audit_report += f\"\"\"\n",
    "{column}:\n",
    "    - 异常值数量: {report['异常值数量']}\n",
    "    - 异常值比例: {report['异常值比例']:.2f}%\n",
    "    - 值域范围: [{report['统计量']['最小值']:.2f}, {report['统计量']['最大值']:.2f}]\n",
    "    - 异常值边界: [{report['异常值边界']['下界']:.2f}, {report['异常值边界']['上界']:.2f}]\n",
    "    - 异常值示例: {report['异常值示例']}\n",
    "\"\"\"\n",
    "\n",
    "audit_report += \"\"\"\n",
    "2. 业务规则违规检测结果：\n",
    "\"\"\"\n",
    "audit_report += f\"\"\"\n",
    "    - 年龄异常: {len(age_violations)} 条\n",
    "    - 工资异常: {len(salary_violations)} 条\n",
    "    - 评分异常: {len(score_violations)} 条\n",
    "    - 物流时效异常: {len(delivery_violations)} 条\n",
    "\"\"\"\n",
    "\n",
    "# 保存审核报告\n",
    "report_file = 'data/processed/outlier_audit_report.txt'\n",
    "with open(report_file, 'w', encoding='utf-8') as f:\n",
    "    f.write(audit_report)\n",
    "print(f\"\\n异常值审核报告已保存至: {report_file}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6b82d7a4-f442-46e3-85e3-afabd94222a8",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-10T15:43:29.796881Z",
     "start_time": "2024-11-10T15:43:28.494887Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 开始异常值处理 ===\n",
      "\n",
      "1. 处理业务规则异常\n",
      "\n",
      "处理年龄异常:\n",
      "已将 5 个异常年龄值替换为中位数: 41\n",
      "\n",
      "处理工资异常:\n",
      "已将 1 个异常工资值替换为中位数: 12564\n",
      "\n",
      "处理评分异常:\n",
      "已将 2 个异常评分值替换为中位数: 85\n",
      "\n",
      "处理物流时效异常:\n",
      "已将 8 个异常物流时效值替换为中位数: 16\n",
      "\n",
      "2. 处理统计异常值\n",
      "\n",
      "订单金额: 已处理 45 个异常值（缩尾处理）\n",
      "\n",
      "客户评分: 已处理 4 个异常值（缩尾处理）\n",
      "\n",
      "物流时效: 已处理 27 个异常值（缩尾处理）\n",
      "\n",
      "评价数量: 已处理 7 个异常值（缩尾处理）\n"
     ]
    }
   ],
   "source": [
    "# 创建数据副本\n",
    "df_outliers_handled = df_standardized.copy()\n",
    "\n",
    "print(\"=== 开始异常值处理 ===\")\n",
    "\n",
    "# 1. 基于业务规则的异常值处理\n",
    "print(\"\\n1. 处理业务规则异常\")\n",
    "\n",
    "# 1.1 处理年龄异常\n",
    "print(\"\\n处理年龄异常:\")\n",
    "age_mask = (df_outliers_handled['年龄'] >= 18) & (df_outliers_handled['年龄'] <= 70)\n",
    "invalid_age = ~age_mask\n",
    "if invalid_age.any():\n",
    "    median_age = int(df_outliers_handled.loc[age_mask, '年龄'].median())  # Convert to int\n",
    "    df_outliers_handled.loc[invalid_age, '年龄'] = median_age\n",
    "    print(f\"已将 {invalid_age.sum()} 个异常年龄值替换为中位数: {median_age}\")\n",
    "\n",
    "# 1.2 处理工资异常\n",
    "print(\"\\n处理工资异常:\")\n",
    "salary_mask = (df_outliers_handled['工资'] >= 3000) & (df_outliers_handled['工资'] <= 50000)\n",
    "invalid_salary = ~salary_mask\n",
    "if invalid_salary.any():\n",
    "    median_salary = int(df_outliers_handled.loc[salary_mask, '工资'].median())  # Convert to int\n",
    "    df_outliers_handled.loc[invalid_salary, '工资'] = median_salary\n",
    "    print(f\"已将 {invalid_salary.sum()} 个异常工资值替换为中位数: {median_salary}\")\n",
    "\n",
    "# 1.3 处理评分异常\n",
    "print(\"\\n处理评分异常:\")\n",
    "score_mask = (df_outliers_handled['客户评分'] >= 0) & (df_outliers_handled['客户评分'] <= 100)\n",
    "invalid_score = ~score_mask\n",
    "if invalid_score.any():\n",
    "    median_score = int(df_outliers_handled.loc[score_mask, '客户评分'].median())  # Convert to int\n",
    "    df_outliers_handled.loc[invalid_score, '客户评分'] = median_score\n",
    "    print(f\"已将 {invalid_score.sum()} 个异常评分值替换为中位数: {median_score}\")\n",
    "\n",
    "# 1.4 处理物流时效异常\n",
    "print(\"\\n处理物流时效异常:\")\n",
    "delivery_mask = (df_outliers_handled['物流时效'] >= 0) & (df_outliers_handled['物流时效'] <= 72)\n",
    "invalid_delivery = ~delivery_mask\n",
    "if invalid_delivery.any():\n",
    "    median_delivery = int(df_outliers_handled.loc[delivery_mask, '物流时效'].median())  # Convert to int\n",
    "    df_outliers_handled.loc[invalid_delivery, '物流时效'] = median_delivery\n",
    "    print(f\"已将 {invalid_delivery.sum()} 个异常物流时效值替换为中位数: {median_delivery}\")\n",
    "\n",
    "# 2. 基于统计的异常值处理\n",
    "print(\"\\n2. 处理统计异常值\")\n",
    "\n",
    "for column in numeric_columns:\n",
    "    # 获取列的数据类型\n",
    "    is_integer = pd.api.types.is_integer_dtype(df_outliers_handled[column].dtype)\n",
    "    \n",
    "    # 计算四分位数\n",
    "    Q1 = df_outliers_handled[column].quantile(0.25)\n",
    "    Q3 = df_outliers_handled[column].quantile(0.75)\n",
    "    IQR = Q3 - Q1\n",
    "    \n",
    "    # 定义异常值边界\n",
    "    lower_bound = Q1 - 1.5 * IQR\n",
    "    upper_bound = Q3 + 1.5 * IQR\n",
    "    \n",
    "    # 如果是整数类型，将边界值转换为整数\n",
    "    if is_integer:\n",
    "        lower_bound = int(np.ceil(lower_bound))  # 向上取整\n",
    "        upper_bound = int(np.floor(upper_bound))  # 向下取整\n",
    "    \n",
    "    # 找出异常值\n",
    "    outliers_mask = (df_outliers_handled[column] < lower_bound) | (df_outliers_handled[column] > upper_bound)\n",
    "    outliers_count = outliers_mask.sum()\n",
    "    \n",
    "    if outliers_count > 0:\n",
    "        # 使用缩尾处理：将异常值设置为边界值\n",
    "        df_outliers_handled.loc[df_outliers_handled[column] < lower_bound, column] = lower_bound\n",
    "        df_outliers_handled.loc[df_outliers_handled[column] > upper_bound, column] = upper_bound\n",
    "        print(f\"\\n{column}: 已处理 {outliers_count} 个异常值（缩尾处理）\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "74936c7b-ff4c-421f-b791-aabec7fef0e8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
